package tu.java.db;


import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.Vector;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import tu.java.constants.DBConst;
import tu.java.exceptions.DBException;

/**
 * This class is a Singleton that provides access to one or many connection
 * pools defined in a Property file. A client gets access to the single
 * instance
 * through the static getInstance() method and can then check-out and
 * check-in
 * connections from a pool. When the client shuts down it should call the
 * release() method to close all open connections and do other clean up.
 * <p>
 * The JNDI name of the DataSource can also be provided. If so, the object will
 * automatically lookup the DataSource after serialization.
 * <p>
 * If, however, the JNDI name is not provided, the object will be unusable after
 * serialization because the DataSource itself is not serialized. Only the JNDI
 * name is serialized.
 */
public class DBConnManager {
	
	
	
	static private DBConnManager	instance;												// The single instance
	private Logger					log		= LoggerFactory.getLogger(DBConnManager.class);
	static private int				clients;
	private Vector					drivers	= new Vector();
	private Hashtable				pools	= new Hashtable();
	
	/** the JNDI name that will be used to lookup the DataSource from the server */
	protected String				jndiDataSourceName;
	
	/** the DataSource that will be used to obtain DB connections */
	protected transient DataSource	dataSource;
	
	
	
	//protected transient UserTransaction userTransaction;
	
	/**
	 * A private constructor since this is a Singleton
	 * 
	 * @throws ResourceException
	 */
	private DBConnManager() {
		init();
	}
	
	
	
	/**
	 * Returns the single instance, creating one if it's the first time this
	 * method is called.
	 * 
	 * @return DBConnManager The single instance.
	 */
	static synchronized public DBConnManager getInstance() {
		if (instance == null) {
			try {
				instance = new DBConnManager();
			}
			catch (Exception e) {
				e.printStackTrace();
			}
		}
		
		clients++;
		
		return instance;
	}
	
	
	
	public void freeConnection(Connection conn) {
		freeConnection(DBConst.DB_POOL_NAME, conn);
	}
	
	
	
	/**
	 * Returns a connection to the named pool.
	 * 
	 * @param name
	 * The pool name as defined in the properties file
	 * @param con
	 * The Connection
	 */
	public void freeConnection(String name, Connection conn) {
		if (DBConst.USE_DS) {
			close(conn);
		}
		else {
			DBConnPool pool = (DBConnPool) pools.get(name);
			
			if (pool != null) {
				pool.freeConnection(conn);
			}
		}
	}
	
	
	
	public Connection getConnection() throws SQLException, DBException {
		return getConnection(DBConst.DB_POOL_NAME, true);
	}
	
	
	
	public Connection getConnection(boolean autoCommit) throws DBException, SQLException {
		return getConnection(DBConst.DB_POOL_NAME, autoCommit);
	}
	
	
	
	/**
	 * Returns an open connection. If no one is available, and the max number of
	 * connections has not been reached, a new connection is created.
	 * 
	 * @param name
	 * The pool name as defined in the properties file
	 * @return Connection The connection or null
	 */
	public Connection getConnection(String name, boolean autoCommit) throws DBException, SQLException {
		Connection conn = null;
		if (DBConst.USE_DS) {
			try {
				DataSource ds = getDataSource();
				if (ds == null)
					throw new DBException("No DataSource provided. Cannot get a connection.");
				conn = getDataSource().getConnection();
			}
			catch (SQLException e) {
				throw new DBException("Failed to get a connection from the DataSource.", e);
			}
		}
		else {
			if (name == "") {
				name = DBConst.DB_POOL_NAME;
			}
			DBConnPool pool = (DBConnPool) pools.get(name);
			
			if (pool != null) {
				conn = pool.getConnection();
			}
			else {
				throw new DBException("Can't get connection from nullable pool: " + name);
			}
		}
		
		conn.setAutoCommit(autoCommit);
		return conn;
	}
	
	
	
	/**
	 * Returns an open connection. If no one is available, and the max number of
	 * connections has not been reached, a new connection is created. If the max
	 * number has been reached, waits until one is available or the specified
	 * time has elapsed.
	 * 
	 * @param name
	 * The pool name as defined in the properties file
	 * @param time
	 * The number of milliseconds to wait
	 * @return Connection The connection or null
	 */
	public Connection getConnection(String name, long time) throws SQLException {
		DBConnPool pool = (DBConnPool) pools.get(name);
		
		if (pool != null) {
			return pool.getConnection(time);
		}
		
		return null;
	}
	
	
	
	/**
	 * Closes all open connections and deregisters all drivers.
	 */
	public synchronized void release() {
		// Wait until called by the last client
		if (--clients != 0) {
			return;
		}
		
		Enumeration allPools = pools.elements();
		
		while (allPools.hasMoreElements()) {
			DBConnPool pool = (DBConnPool) allPools.nextElement();
			pool.release();
		}
		
		Enumeration allDrivers = drivers.elements();
		
		while (allDrivers.hasMoreElements()) {
			Driver driver = (Driver) allDrivers.nextElement();
			
			try {
				DriverManager.deregisterDriver(driver);
				log.info("Deregistered JDBC driver <" + driver.getClass().getName() + ">");
			}
			catch (SQLException e) {
				log.error("Can't deregister JDBC driver <{}>", driver.getClass().getName(), e);
			}
		}
	}
	
	
	
	/**
	 * Creates instances of DBConnPool based on the properties.
	 * A DBConnPool can be defined with the following properties:
	 * 
	 * <PRE>
	 * &lt;poolname&gt;.url         The JDBC URL for the database
	 * &lt;poolname&gt;.user        A database user (optional)
	 * &lt;poolname&gt;.password    A database user password (if user specified)
	 * &lt;poolname&gt;.maxconn     The maximal number of connections (optional)
	 * </PRE>
	 * 
	 * @param props
	 * The connection pool properties
	 */
	private void createPools() {
		//Enumeration propNames = props.propertyNames();
		//while (propNames.hasMoreElements()) {
		String poolName = DBConst.DB_POOL_NAME;
		String url = DBConst.DB_URL;
		if (url == null)
			log.debug("No URL specified for " + poolName);
		String user = DBConst.DB_USER;
		String password = DBConst.DB_PASS;
		int maxconn = DBConst.DB_MAX_CONN;
		DBConnPool pool = new DBConnPool(this, poolName, url, user, password, maxconn, "Sequence");
		pools.put(poolName, pool);
		log.debug("Initialized pool " + poolName);
		//}
	}
	
	
	
	private void init() {
		if (DBConst.USE_DS) {
			this.jndiDataSourceName = DBConst.JNDI_DS_NAME;
		}
		else {
			loadDrivers();
			createPools();
		}
	}
	
	
	
	/**
	 * Loads and registers all JDBC drivers. This is done by the
	 * DBConnManager, as opposed to the DBConnPool,
	 * since many pools may share the same driver.
	 * 
	 * @param props
	 * The connection pool properties
	 */
	private void loadDrivers() {
		String driverClassName = DBConst.DB_DRIVER_CLASS;
		LoadDriver(driverClassName);
	}
	
	
	
	public void LoadDriver(String sDriverClassName) {
		// Try to register the JDBC driver
		try {
			Driver driver = (Driver) Class.forName(sDriverClassName).newInstance();
			DriverManager.registerDriver(driver);
			drivers.addElement(driver);
			log.info("New JDBC driver registered <" + sDriverClassName + ">");
		}
		catch (Exception e) {
			log.error("Can't register JDBC driver <{}>", sDriverClassName, e);
		}
	}
	
	
	
	public void AddPool(String sPoolName, String sUrl, String sUser, String sPwd, int iMax, String sSeqImpl) {
		DBConnPool pool = new DBConnPool(this, sPoolName, sUrl, sUser, sPwd, iMax, sSeqImpl);
		pools.put(sPoolName, pool);
		log.info("New pool added <" + sPoolName + ">");
	}
	
	
	
	public DBConnPool getPool(String sPoolName) {
		DBConnPool pool = (DBConnPool) pools.get(sPoolName);
		
		return pool;
	}
	
	
	
	/**
	 * Set a JNDI name for the DataSource
	 * 
	 * @param jndiDataSourceName
	 * the JNDI name of the DataSource
	 * @throws NullPointerException
	 * if a <code>null</code> or empty name has been provided
	 */
	public synchronized void setJndiDataSourceName(String jndiDataSourceName) {
		//if (jndiDataSourceName == null && jndiDataSourceName.equals(""))
		//    throw new NullPointerException("Empty JNDI DataSource name specified.");
		
		if (jndiDataSourceName == null || !jndiDataSourceName.equals(this.jndiDataSourceName)) {
			this.jndiDataSourceName = jndiDataSourceName;
			this.dataSource = null;  // reset it now; we'll look it up the first time we need it
		}
	}
	
	
	
	/**
	 * Get the JNDI name of the DataSource that will be used for obtaining DB
	 * connections
	 */
	public synchronized String getJndiDataSourceName() {
		return jndiDataSourceName;
	}
	
	
	
	/**
	 * Set the DataSource that will be used for obtaining DB connections
	 * 
	 * @param dataSource
	 * the DataSource
	 */
	public synchronized void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
	
	
	
	/**
	 * Returns the DataSource. If a DataSource has not been provided, but if a
	 * JNDI
	 * name for the DataSource has been provided, an attempt is made to lookup
	 * the
	 * DataSource from the server.
	 * 
	 * @return a DataSource capable of providing JDBC connections
	 * @throws DBException
	 * if the JNDI lookup fails
	 */
	public synchronized DataSource getDataSource() throws DBException {
		if (this.dataSource == null) {
			if (this.jndiDataSourceName != null)
				this.dataSource = lookupDataSource(this.jndiDataSourceName);
		}
		return this.dataSource;
	}
	
	
	
	/**
	 * Returns the UserTransaction. If a UserTransaction has not been provided,
	 * but if a JNDI
	 * name for the UserTransaction has been provided, an attempt is made to
	 * lookup the
	 * UserTransaction from the server.
	 * 
	 * @return a UserTransaction capable of providing JDBC connections
	 * @throws SecurityException
	 * if the JNDI lookup fails
	 */
//	    public synchronized UserTransaction getUserTransaction() throws DBException {
//	        if (this.userTransaction == null) {
//	            if (this.jndiDataSourceName != null)
//	                this.userTransaction = lookupUserTransaction(this.jndiDataSourceName);
//	        }
//	        return this.userTransaction;
//	    }
	
	public synchronized boolean hasDataSource() {
		try {
			return (getDataSource() != null);
		}
		catch (DBException e) {
			return false;
		}
	}
	
	
	
	/**
	 * Lookup the DataSource instance from the JNDI context.
	 * 
	 * @param jndiDataSourceName
	 * the JNDI name of the DataSource
	 * @return a DataSource obtained from the server
	 * @throws SecurityException
	 * if the JNDI lookup fails
	 */
	protected synchronized DataSource lookupDataSource(String jndiDataSourceName) throws DBException {
		DataSource dataSource = null;
		try {
			Context ictx = new InitialContext();
			Context envctx = (Context) ictx.lookup(jndiDataSourceName);
			dataSource = (DataSource) envctx.lookup(DBConst.DB_NAME);
		}
		catch (NamingException e) {
			throw new DBException("Failed to lookup the DataSource.", e);
		}
		catch (ClassCastException e) {
			throw new DBException("The JNDI name '" + jndiDataSourceName + "' doesn't specify a DataSource.", e);
		}
		return dataSource;
	}
	
	
	
	/**
	 * Lookup the UserTransaction instance from the JNDI context.
	 * 
	 * @param jndiDataSourceName
	 * the JNDI name of the DataSource
	 * @return a UserTransaction obtained from the server
	 * @throws SecurityException
	 * if the JNDI lookup fails
	 */
//	    protected synchronized UserTransaction lookupUserTransaction(String jndiDataSourceName) throws DBException {
//	    	UserTransaction ut = null;
//	        try {
//				Context ictx = new InitialContext();
//				Context envctx = (Context) ictx.lookup(jndiDataSourceName);
//				ut = (UserTransaction)envctx.lookup("java:comp/UserTransaction");
//	        } catch(NamingException e) {
//	            throw new DBException("Failed to lookup the UserTransaction.", e);
//	        } catch(ClassCastException e) {
//	            throw new DBException("The JNDI name '" + jndiDataSourceName + "' doesn't specify a UserTransaction.", e);
//	        }
//	        return ut;
//	    }
	
	/**
	 * Close a connection
	 * 
	 * @param conn
	 * JDBC connection
	 */
	private void close(Connection conn) {
		close(conn, null, null, null);
	}
	
	
	
	/**
	 * Close a connection and a statement
	 * 
	 * @param conn
	 * JDBC connection
	 * @param stmt
	 * JDBC statement
	 */
//	    protected static void close(Connection conn, Statement stmt) {
//	        close(conn, stmt, null);
//	    }
	
	/**
	 * Close a statement
	 * 
	 * @param stmt
	 * JDBC statement
	 */
	public void close(ResultSet rs) {
		close(null, null, null, rs);
	}
	
	
	
	/**
	 * Close a statement
	 * 
	 * @param stmt
	 * JDBC statement
	 */
	public void close(Statement stmt) {
		close(null, null, stmt, null);
	}
	
	
	
	/**
	 * Close a statement
	 * 
	 * @param pstmt
	 * JDBC prepared statement
	 */
	public void close(PreparedStatement pstmt) {
		close(null, pstmt, null, null);
	}
	
	
	
	/**
	 * Close a statement and a result set
	 * 
	 * @param stmt
	 * JDBC statement
	 * @param rs
	 * JDBC result set
	 */
	public void close(Statement stmt, ResultSet rs) {
		close(null, null, stmt, rs);
	}
	
	
	
	/**
	 * Close a statement and a result set
	 * 
	 * @param pstmt
	 * JDBC prepared statement
	 * @param stmt
	 * JDBC statement
	 * @param rs
	 * JDBC result set
	 */
	public void close(PreparedStatement pstmt, Statement stmt, ResultSet rs) {
		close(null, pstmt, stmt, rs);
	}
	
	
	
	/**
	 * Close a connection, a statement and a result set
	 * 
	 * @param conn
	 * JDBC connection
	 * @param stmt
	 * JDBC statement
	 * @param rs
	 * JDBC result set
	 */
	private void close(Connection conn, PreparedStatement pstmt, Statement stmt, ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			}
			catch (SQLException e) {
				log.error(">>>>>>ib.db.DBConnManager Failed to close JDBC result set.", e);
			}
			rs = null;
		}
		if (stmt != null) {
			try {
				stmt.close();
			}
			catch (SQLException e) {
				log.error(">>>>>>ib.db.DBConnManager Failed to close JDBC statement.", e);
			}
			stmt = null;
		}
		if (pstmt != null) {
			try {
				pstmt.close();
			}
			catch (SQLException e) {
				log.error(">>>>>>ib.db.DBConnManager Failed to close JDBC prepare statement.", e);
			}
			pstmt = null;
		}
		if (conn != null) {
			try {
				conn.close();
			}
			catch (SQLException e) {
				log.error(">>>>>>ib.db.DBConnManager Failed to close JDBC connection.", e);
			}
			conn = null;
		}
	}
	
	
	
	/**
	 * Rolls back a connection
	 * 
	 * @param conn
	 * a JDBC connection that must be rolled back
	 */
	public void rollback(Connection conn) {
		if (conn != null) {
			try {
				conn.rollback();
			}
			catch (SQLException e) {
				log.debug(">>> " + this.getClass().getName() + " " + "Failed to rollback a transaction.");
				e.printStackTrace();
			}
		}
	}
	
	
	
	/**
	 * Commit a connection
	 * 
	 * @param conn
	 * a JDBC connection that must be commit
	 */
	public void commit(Connection conn) {
		if (conn != null) {
			try {
				conn.commit();
			}
			catch (SQLException e) {
				log.debug(">>> " + this.getClass().getName() + " " + "Failed to commit a transaction.");
				e.printStackTrace();
			}
		}
	}
	
	
	
	@Override
	public String toString() {
		return "DBConnector: jndiDataSourceName=" + jndiDataSourceName + ", dataSource=" + dataSource;
	}
	
}
